package com.supermap.dataservice.monitor.dao;

import com.supermap.dubbo.api.monitor.entity.Active;
import com.supermap.dubbo.api.monitor.entity.AgrCrop;
import com.supermap.dubbo.api.monitor.entity.SerObj;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;

import java.util.List;
import java.util.Map;

/**
 * @author lxs
 * @Title:监控平台数据访问层
 * @Description:
 * @date 2018/7/11  11:16
 */
@Mapper
public interface MonitorDao {
    //当年月各类作物农情上传的数量
    @Select("select t1.id,t2.type,MONTH(t1.createtime) as mon,COUNT(t2.type) as nums from t_agrinfo t1 \n" +
            " INNER JOIN t_crop_type t2 on t1.type=t2.cropid where t1.areacode like #{areacode}  and t1.del_flg=0\n" +
            " and YEAR(t1.createtime)=#{year} GROUP BY t2.type,MONTH(t1.createtime) ORDER BY mon")
    List<AgrCrop> getAgrCropsByAreacode(@Param("areacode")String areacode, @Param("year")Integer year);

    @Select("select type from t_crop_type GROUP BY type")
    List<String> getCropsTypesList();

    //各类服务对象总数量
    @Select("select t3.name,COUNT(t3.name) as nums from t_user t1 INNER JOIN t_user_role t2  on t1.id=t2.userid " +
            "INNER JOIN t_role t3 on t2.roleid=t3.id  where t1.areaCode like #{areaCode}  GROUP BY t3.name")
    List<SerObj> getSerObjListByAreacode(@Param("areaCode") String areaCode);

    @Select("select id,name,code from t_role")
    List<SerObj> getRoleList();
    //各类服务对象当月新增总数量
    @Select("select t3.name,COUNT(t3.name) as nums from t_user t1 INNER JOIN t_user_role t2  on t1.id=t2.userid " +
            "INNER JOIN t_role t3 on t2.roleid=t3.id  where t1.areaCode like #{areaCode} AND YEAR(t1.createtime)=#{year} and MONTH(t1.createtime)=#{mon} GROUP BY t3.name")
    List<SerObj> getSerObjListByAreacodeAndYearMonth(@Param("areaCode") String areaCode, @Param("year")Integer year, @Param("mon")Integer mon);
    //专家活跃度(含咨询，灾情等)
    @Select("\t\t\t\t\t\t\tselect t1.id,t1.username,t1.mobile,t1.HeadImage,t5.areaname,t1.Introduction,COUNT(t1.id) as nums from t_user t1\n" +
            "         \tinner join t_expertinfo t2 on t2.userid=t1.id\n" +
            "            inner join (SELECT * FROM t_advcomment UNION all SELECT * FROM t_agrinfocomment )t4 on t1.id=t4.userId \n" +
            "            inner join t_area t5 on t1.areaCode=t5.areacode\n" +
            "            where  t1.areaCode like #{areaCode} group by t1.id order by  nums desc")
    List<Active> getActiveListByareaCode(@Param("areaCode") String areaCode);

    @Select("select t4.id,t1.id as commentid,t1.username,t1.mobile,t1.HeadImage,t5.areaname,t1.Introduction,t4.comment,t4.parentId,t4.commentTime  from t_user t1 " +
            "INNER JOIN t_user_role t2  on t1.id=t2.userid " +
            "INNER JOIN t_role t3 on t2.roleid=t3.id " +
            "inner join (SELECT * FROM t_advcomment UNION all SELECT * FROM t_agrinfocomment )t4 on t1.id=t4.userId  " +
            "inner join t_area t5 on t1.areaCode=t5.areacode   " +
            "where t1.id=#{userId} GROUP BY t4.parentId order by t4.commentTime desc")
    List<Active> getTreeList(@Param("userId") String userId);

    @Select("select t4.id,t1.id as commentid,t1.username,t1.mobile,t1.HeadImage,t1.Introduction,t4.comment,t4.parentId,t4.commentTime  from t_user t1 " +
            " INNER JOIN t_user_role t2  on t1.id=t2.userid " +
            " INNER JOIN t_role t3 on t2.roleid=t3.id " +
            " inner join (SELECT * FROM t_advcomment UNION all SELECT * FROM t_agrinfocomment )t4 on t1.id=t4.userId " +
            "where t4.id=#{id} ")
    Active getPComment(@Param("id") String id);

    /**
     * 当年上传每月的灾情统计
     * @param year
     * @param areaCode
     * @return
     */
//    @Select("select id,MONTH(createtime)as mon,COUNT(1) as nums from t_disaster \n" +
//            "where areacode like #{areaCode} and YEAR(createtime)=#{year} GROUP BY MONTH(createtime) ORDER BY mon")
    @SelectProvider(type = MonitorDaoSQL.class,method ="getDisListSQL" )
    List<AgrCrop> getDisList(@Param("beginTime")String beginTime,@Param("endTime")String endTime,@Param("areaCode") String areaCode,@Param("cropId")String cropId);

    /**
     * 当年上传每月的咨询统计
     * @param year
     * @param areaCode
     * @return
     */
//    @Select("select id,MONTH(uploadTime)as mon,COUNT(1) as nums from t_advisoryinfo \n" +
//            "where areacode like #{areaCode} and YEAR(uploadTime)=#{year} GROUP BY MONTH(uploadTime) ORDER BY mon")
    @SelectProvider(type = MonitorDaoSQL.class,method ="getAdvListSQL" )
    List<AgrCrop> getAdvList(@Param("beginTime")String beginTime,@Param("endTime")String endTime,@Param("areaCode") String areaCode,@Param("cropId")String cropId);

    /**
     * 当年上传每月农情统计
     * @param year
     * @param areaCode
     * @return
     */
//    @Select("select id,MONTH(createtime)as mon,COUNT(1) as nums from t_agrinfo \n" +
//            "where areacode like #{areaCode} and YEAR(createtime)=#{year} GROUP BY MONTH(createtime) ORDER BY mon")
    @SelectProvider(type = MonitorDaoSQL.class,method ="getAgrListSQL" )
    List<AgrCrop> getAgrList(@Param("beginTime")String beginTime,@Param("endTime")String endTime,@Param("areaCode") String areaCode,@Param("cropId")String cropId);

    class MonitorDaoSQL{
         public String getDisListSQL(Map<String,Object> map){
              String areaCode= (String)map.get("areaCode");
             String cropId= (String)map.get("cropId");
             String beginTime=(String)map.get("beginTime");
             String endTime=(String)map.get("endTime");
              StringBuilder stringBuilder=new StringBuilder();
              stringBuilder.append(" select t1.id,t1.yeara,t1.mon,sum(t1.nums) as  nums from \n" +
                      "(select id,year(createtime) as yeara,MONTH(createtime)as mon,COUNT(1) as nums from t_disaster  where 1=1 ");
             if(areaCode!=null){
                 if(!areaCode.equals("1%")){
                     stringBuilder.append("  and   areacode like '"+areaCode+"'  ");
                 }
             }
             if(cropId!=null){
                 stringBuilder.append(" and  (breed="+cropId+" or type="+cropId+") ");
             }
             stringBuilder.append(" and createtime between '"+beginTime+"' and '"+endTime+"'  GROUP BY MONTH(createtime)   union all " +
                     "           select id,year(createtime) as yeara,MONTH(createtime)as mon,COUNT(1) as nums from t_disaster_dip  where 1=1 ");
             if(areaCode!=null){
                 if(!areaCode.equals("1%")){
                     stringBuilder.append("  and   areacode like '"+areaCode+"'  ");
                 }
             }
             if(cropId!=null){
                 stringBuilder.append(" and  (breed="+cropId+" or type="+cropId+") ");
             }
             stringBuilder.append(" and createtime between '"+beginTime+"' and '"+endTime+"'   GROUP BY MONTH(createtime) ) t1 group by t1.mon asc ORDER BY yeara,mon asc " );
              return  stringBuilder.toString();
         }
        public String getAdvListSQL(Map<String,Object> map){
            String areaCode= (String)map.get("areaCode");
            String cropId= (String)map.get("cropId");
            String beginTime=(String)map.get("beginTime");
            String endTime=(String)map.get("endTime");
            StringBuilder stringBuilder=new StringBuilder();
            stringBuilder.append("select id,year(uploadTime) as yeara,MONTH(uploadTime)as mon,COUNT(1) as nums from t_advisoryinfo " +
                    "  where 1=1 ");
            if(areaCode!=null){
                if(!areaCode.equals("1%")){
                    stringBuilder.append("  and   areacode like '"+areaCode+"'  ");
                }
            }
            if(cropId!=null){
                stringBuilder.append(" and  (breed="+cropId+" or type="+cropId+") ");
            }
            stringBuilder.append(" and uploadTime between '"+beginTime+"' and '"+endTime+"'  GROUP BY MONTH(uploadTime) ORDER BY yeara,mon asc ");
            return  stringBuilder.toString();
        }
        public String getAgrListSQL(Map<String,Object> map){
            String areaCode= (String)map.get("areaCode");
            String cropId= (String)map.get("cropId");
            String beginTime=(String)map.get("beginTime");
            String endTime=(String)map.get("endTime");
            StringBuilder stringBuilder=new StringBuilder();
            stringBuilder.append("select id,year(createtime) as yeara,MONTH(createtime)as mon,COUNT(1) as nums from t_agrinfo " +
                    "   where 1=1 ");
            if(areaCode!=null){
                if(!areaCode.equals("1%")){
                    stringBuilder.append("  and   areacode like '"+areaCode+"'  ");
                }
            }
            if(cropId!=null){
                stringBuilder.append(" and  (breed="+cropId+" or type="+cropId+") ");
            }
            stringBuilder.append("   and createtime between '"+beginTime+"' and '"+endTime+"'  GROUP BY MONTH(createtime) ORDER BY yeara,mon asc ");
            return  stringBuilder.toString();
        }
    }
}
